package com.example.backend.mapper;

import com.example.backend.pojo.Bili;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface BiliMapper {

    @Select("SELECT\n" +
            "    SUM(CASE WHEN u.gender = 1 THEN 1 ELSE 0 END) AS number1,\n" +
            "    SUM(CASE WHEN u.gender = 0 THEN 1 ELSE 0 END) AS number2,\n" +
            "    COUNT(*) AS total\n" +
            "FROM (\n" +
            "        SELECT DISTINCT fo.userid\n" +
            "        FROM foodorder fo\n" +
            "        WHERE fo.restaurantid LIKE CONCAT('%',#{restaurantid},'%')\n" +
            ") AS unique_users\n" +
            "JOIN user u ON unique_users.userid = u.userid\n" +
            "WHERE u.gender IS NOT NULL;")
    Bili sexratio(String restaurantid);



    @Select("SELECT\n" +
            "    SUM(CASE WHEN u.age < 10 THEN 1 ELSE 0 END) AS number1,\n" +
            "    SUM(CASE WHEN u.age >= 10 AND u.age < 20 THEN 1 ELSE 0 END) AS number2,\n" +
            "    SUM(CASE WHEN u.age >= 20 AND u.age < 30 THEN 1 ELSE 0 END) AS number3,\n" +
            "    SUM(CASE WHEN u.age >= 30 AND u.age < 40 THEN 1 ELSE 0 END) AS number4,\n" +
            "    SUM(CASE WHEN u.age >= 40 AND u.age < 50 THEN 1 ELSE 0 END) AS number5,\n" +
            "    SUM(CASE WHEN u.age >= 50 AND u.age < 60 THEN 1 ELSE 0 END) AS number6,\n" +
            "    SUM(CASE WHEN u.age >= 60 THEN 1 ELSE 0 END) AS number7,\n" +
            "    COUNT(*) AS total,\n" +
            "    AVG(u.age) AS avg\n" +
            "FROM (\n" +
            "    SELECT DISTINCT fo.userid\n" +
            "    FROM foodorder fo\n" +
            "    WHERE fo.restaurantid LIKE CONCAT('%',#{restaurantid},'%')\n" +
            ") AS unique_users\n" +
            "JOIN user u ON unique_users.userid = u.userid\n" +
            "WHERE u.age IS NOT NULL;")
    Bili ageratio(String restaurantid);


    @Select("SELECT\n" +
            "    IFNULL(SUM(CASE WHEN fo.way = 1 THEN 1 ELSE 0 END), 0) AS number1,\n" +
            "    IFNULL(SUM(CASE WHEN fo.way = 2 THEN 1 ELSE 0 END), 0) AS number2,\n" +
            "    COUNT(*) AS total\n" +
            "FROM\n" +
            "    foodorder fo\n" +
            "WHERE\n" +
            "    fo.restaurantid LIKE CONCAT('%',#{restaurantid},'%');")
    Bili wayratio(String restaurantid);


    @Select("SELECT\n" +
            "    COUNT(CASE WHEN fo.reviewid IS NOT NULL THEN 1 END) AS number1,\n" +
            "    COUNT(CASE WHEN fo.complainid IS NOT NULL THEN 1 END) AS number2,\n" +
            "    COUNT(*) AS total\n" +
            "FROM\n" +
            "    foodorder fo\n" +
            "WHERE\n" +
            "    fo.restaurantid LIKE CONCAT('%',#{restaurantid},'%');\n")
    Bili buyratio(String restaurantid);

}
